package com.ibm.risk.irmp.report.service.impl;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ibm.risk.irmp.common.utils.DateUtils;
import com.ibm.risk.irmp.common.utils.XlsxUtils;
import com.ibm.risk.irmp.report.dao.GenericReportDataRepository;
import com.ibm.risk.irmp.report.entity.ReportData;
import com.ibm.risk.irmp.report.service.IReportContentService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static org.apache.poi.ss.usermodel.CellType.FORMULA;

@Slf4j
@Service("GenericReportService")
public class GenericXlsxReportContentServiceImpl implements IReportContentService {
    public static final String DATA_ROW_START = "DATA_ROW_START";
    public static final String ITEM_PRE = "ITEM_";
    public static final String ROW_CODE_PRE = "ROW_CODE:";
    public static final String REPORT_ORG_VAR = "RPT_ORG";
    public static final String DATA_DATE_VAR = "DAT_DT";
    public static final String VAR_DELIMITER = "%";
    private static final String DATA_YEAR_VAR = "DAT_YR";
    private static final String DATA_MONTH_VAR = "DAT_MN";
    private static final String DATA_DAY_VAR = "DAT_DY";
    private static final String DATA_SEASON_VAR = "DAT_SSN";
    private static final String DATA_SEMI_YEAR_VAR = "DAT_SMY";

    public static final int MAX_PAST_PERIODS = 9;


    @Autowired
    GenericReportDataRepository genericReportDataRepository;
    @Autowired
    ObjectMapper om;

    /**
     * 生成Excel报表
     */
    @Override
    public Workbook generate(String reportCode, File templateFile, String dataDate, String batchId, String branchCode, boolean isPreview) throws Exception {
        //读取报表数据
        List<ReportData> data1s = genericReportDataRepository.findByReportCodeAndDataDateAndBatchNoAndBranchCode(
                reportCode, dataDate, batchId, branchCode);
        if (CollectionUtils.isEmpty(data1s)) {
            throw new RuntimeException(String.format("No data found for report %s-%s-%s-%s", reportCode, dataDate, batchId, batchId));
        }
//      List<Map<String, Object>> data1 = reportDataService.getSimpleReportData(reportCode, dataDate, batchId, branchCode);
        Map<String, Map<String, Object>> datas = data1s.stream().collect(Collectors.toMap(r -> r.getItemCode(), r -> jsonToMap(r.getRowData())));
        //生成Excel报表
        Workbook workbook = XlsxUtils.getWorkbook(templateFile);
        Sheet sheet = workbook.getSheet(reportCode);
        if (sheet == null) {
            sheet = workbook.getSheetAt(0);
        }
        setReportHead(dataDate, branchCode, sheet);
        String[] colNames = null;
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //check if data_row_start
            Cell cell0 = row.getCell(0);
            if (cell0 == null) {
                continue;
            }
            Comment comment = cell0.getCellComment();
            if (comment != null) {
                List<String> stCmnts = getCommentLines(comment);
                if (isDataStartRow(stCmnts)) {
                    colNames = new String[row.getLastCellNum()];
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        colNames[j] = extractColCode(sheet, i, j);
                        if (colNames[j] != null) {
                            colNames[j] = colNames[j].toLowerCase();
                        }
                    }
                }
            }
            String itemCode = getRowCode(cell0);
            if (StringUtils.isEmpty(itemCode)) {
                continue;
            }
            Map<String, Object> map = datas.get(itemCode);
            if (map == null || colNames == null) {
                continue;
            }
            for (int c = 0; c < row.getLastCellNum(); c++) {
                if (c >= colNames.length) break;
                if (colNames[c] == null) {
                    continue;
                }
                Object dbValObj = map.get(colNames[c]);
                if (dbValObj == null) continue;
                Cell cell = row.getCell(c);
                if (cell == null) continue;
                log.debug("set value {} to cell {}-{}", dbValObj, i, c);
                setCellValue(dbValObj, cell);
            }
            if (comment != null) cell0.removeCellComment();
        }
        removeComments(sheet);
        //触发公式重新计算
        XlsxUtils.calculate(workbook);
        return workbook;
    }

    private Map<String, Object> jsonToMap(String json) {
        try {
            return om.readValue(json, Map.class);
        } catch (JsonProcessingException e) {
            log.error("Json parse error: {}", json, e);
        }
        return null;
    }

    private void removeComments(Sheet sheet) {
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellComment() != null) {
                    cell.removeCellComment();
                }
            }
        }
    }

    private String extractColCode(Sheet sheet, int i, int j) {
        Comment colCmnt = getItemComment(sheet, i, j);
        if (colCmnt != null) {
            List<String> stCmnts = getCommentLines(colCmnt);
            log.trace("Cell {}-{} comment: {}", i, j, stCmnts);
            for (String stCmnt : stCmnts) {
                stCmnt = stCmnt.trim();
                if (stCmnt.startsWith(ITEM_PRE)) {

                    return stCmnt.toUpperCase();
                }
            }
        } else {
            Cell cell = sheet.getRow(i).getCell(j);
            if (cell != null && cell.getCellType() == CellType.STRING) {
                String cellValue = cell.getStringCellValue();
                if (cellValue.matches("^[a-zA-Z]{1,2}$")) {
                    return ITEM_PRE + cellValue.toUpperCase();
                }
            }
        }
        return null;
    }

    private boolean isDataStartRow(List<String> cmnts) {
        if (cmnts != null) {
            for (String cmnt : cmnts) {
                if (DATA_ROW_START.equalsIgnoreCase(cmnt.trim())) {
                    return true;
                }
            }
        }
        return false;
    }

    private void setCellValue(Object dbValObj, Cell cell) {
        CellType cellType = cell.getCellType();
        if (XlsxUtils.isGreyCell(cell) || cellType == FORMULA) {
            if (ObjectUtils.isNotEmpty(dbValObj)) {
                log.warn("DB value [{}] ignored due to formula/grey cell.", dbValObj);
            }
            return;
        }

        String df = cell.getCellStyle().getDataFormatString();
        if (df == null) {
            df = "";
        } else if ("".equals(df)) {
            //Cell is Text Format, convert number to text
            if (dbValObj instanceof Number) {
                dbValObj = String.valueOf(((Number) dbValObj).intValue());
            }
        }
//        StylesTable stylesTable = ((XSSFWorkbook) cell.getSheet().getWorkbook()).getStylesSource();
//        CellStyle cellstyle1 = cell.getCellStyle();
//        cellstyle1 = stylesTable.getStyleAt(cellstyle1.getIndex());
//        XSSFColor b = ((XSSFCellStyle) cellstyle1).getFillForegroundXSSFColor();
//        String rgb = b == null ? "" : b.getARGBHex();
        if (cellType == CellType.NUMERIC || df.contains("#") || df.contains("0") || dbValObj instanceof Number) {
            double v = cell.getNumericCellValue();
            double itemVal;
            if (dbValObj instanceof Number) {
                itemVal = ((Number) dbValObj).doubleValue();
            } else {
                itemVal = Double.parseDouble(dbValObj.toString());
            }
            if (v != 0.0) {
                itemVal = itemVal / v;
            }
            cell.setCellValue(itemVal);
        } else {
            cell.setCellValue(dbValObj.toString());
        }
    }

    private Comment getItemComment(Sheet sheet, int row, int col) {
        //倒查3格，找comment，应对3行合并情况
        for (int i = 0; i < 3; i++) {
            if (row - i < 0) break;
            Row headRow = sheet.getRow(row - i);
            if (headRow == null) break;
            Cell cell = headRow.getCell(col);
            if (cell == null) continue;
            Comment comment = cell.getCellComment();
            if (comment == null) {
                continue;
            }
            return comment;
        }
        return null;
    }

    Pattern p = Pattern.compile(ROW_CODE_PRE + "[ ]*\\d+[\\.\\d+]*[a-zA-Z]?");

    private String getRowCode(Cell cell) {
        Comment comment = cell.getCellComment();
        if (comment != null) {
            List<String> stCmnts = getCommentLines(comment);
            for (String stCmnt : stCmnts) {
                stCmnt = stCmnt.trim();
                //row_\d+[\.\d+]*
                // "data_row_start\r\nrow:1.1"
                Matcher matcher = p.matcher(stCmnt);
                if (matcher.find()) {
                    String rowCode = matcher.group(0);
                    log.debug("find row code in comment {} : {}", stCmnt, rowCode);
                    return rowCode.substring(ROW_CODE_PRE.length()).trim();
                }
            }
        }
        return XlsxUtils.getCellStrValue(cell);
    }


    private List<String> getCommentLines(Comment comment) {
        RichTextString richTextString = comment.getString();
        String cmnts = richTextString.getString();
        return Arrays.asList(cmnts.split("\r\n|\r|\n"));
    }

    protected void setReportHead(String dataDate, String branchCode, Sheet sheet) {
        Map<String, String> varMap = createVarMap(dataDate, branchCode);
        for (int r = 0; r < sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String cval = cell.getStringCellValue();
                    int vdIdx = cval.indexOf(VAR_DELIMITER);
                    if (vdIdx >= 0 && cval.lastIndexOf(VAR_DELIMITER) > vdIdx) {
                        boolean replaced = false;
                        for (String varName : varMap.keySet()) {
                            if (cval.contains(varName)) {
                                cval = cval.replace(varName, varMap.get(varName));
                                replaced = true;
                            }
                        }
                        if (replaced) {
                            cell.setCellValue(cval);
                        } else if (Pattern.matches("\\w+", cval)) {
                            log.warn("May has unkown variable {}", cval);
                        }
                    }
                }
            }
        }
    }

    // Cell dateCell = sheet.getRow(x2).getCell(y2);
    //setReportDate(dataDate, branchCode, branchCell, dataCell);
    protected void setReportDate(String dataDate, String branchCode, Cell brachCell, Cell dateCell) {
        createVarMap(dataDate, branchCode);
    }

    private Map<String, String> createVarMap(String dataDate, String branchCode) {
        Map<String, String> dataMap = new LinkedHashMap<>();
        //数据机构
        String bizBranchName = "TEST"; //reportDataService.getBizBranchName(branchCode);
        dataMap.put(VAR_DELIMITER + REPORT_ORG_VAR + VAR_DELIMITER, bizBranchName);
        String reportDateCN = DateUtils.getDayStr(dataDate);
        dataMap.put(VAR_DELIMITER + DATA_DATE_VAR + VAR_DELIMITER, dataDate);
        int year = Integer.parseInt(dataDate.substring(0, 4));
        dataMap.put(VAR_DELIMITER + DATA_YEAR_VAR + VAR_DELIMITER, String.valueOf(year--));
        String monthStr = dataDate.substring(4, 6);
        dataMap.put(VAR_DELIMITER + DATA_MONTH_VAR + VAR_DELIMITER, monthStr);
        dataMap.put(VAR_DELIMITER + DATA_DAY_VAR + VAR_DELIMITER, dataDate.substring(6));
        int iMonth = Integer.parseInt(monthStr);
        dataMap.put(VAR_DELIMITER + DATA_SEASON_VAR + VAR_DELIMITER, String.valueOf(iMonth / 3));
        dataMap.put(VAR_DELIMITER + DATA_SEMI_YEAR_VAR + VAR_DELIMITER, iMonth >= 6 ? "下" : "上");
        //比较期报告日期生成
        for (int i = 1; i <= MAX_PAST_PERIODS; i++) {
            dataMap.put(VAR_DELIMITER + DATA_YEAR_VAR + "-" + i + VAR_DELIMITER, String.valueOf(year--));
        }
        return dataMap;
    }

    /**
     * 生成Excel报表并转换成HTML
     */
    @Override
    public Map<String, Object> convert2Html(String reportCode, File templateFile, String dataDate, String batchId, String branchCode) throws Exception {
        Map<String, Object> result = new HashMap<>();
        //生成Excel报表
        Workbook workbook = generate(reportCode, templateFile, dataDate, batchId, branchCode, true);
        // 转换成HTML
        String html = XlsxUtils.readExcelToHtml(workbook, true);
        result.put("html", html);
        return result;
    }
}
